package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Student;
import util.ConnectionUtil;


public class StudentDao {
	
	public static ArrayList<Student> queryStudentList() {
		ArrayList<Student> studentList = new ArrayList<>();
		
		Connection conn = ConnectionUtil.getConnection();
        Statement stmt = null;
        
        try{
            // 执行查询
            //System.out.println(" 实例化Statement对象...");
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT id, name, age,sex,classname,majorname FROM studb join class join major on class.classid=studb.classid and major=majorid";
            ResultSet rs = stmt.executeQuery(sql);
        
            //展开结果集数据库
            while(rs.next()){
                // 通过字段检索
                int id  = rs.getInt("id");
                int sex = rs.getInt("sex");
                String gender = "男";
                if(sex==1) {
                	gender = "男";
                }else {
                	gender = "女";
                }
                String majorname=rs.getString("majorname");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String classname = rs.getString("classname");

                
                Student student = new Student(id,name,gender,age,classname,majorname);
                studentList.add(student);
                
            }
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }// 什么都不做
            try{
                if(conn!=null) 
                	conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        
        System.out.println("查询成功！");
        return studentList;
	
	}

	//添加学生
	public static void addUseStatement(Student student) {
		Connection connection = ConnectionUtil.getConnection();
		try {
			Statement statement = connection.createStatement();
			
			int gender = 1;
			if(student.getGender().equals("男")) {
				gender = 1;
			}else {
				gender = 0;
			}

			int class_id = ClassDao.getClassId(student.getClassname());
			int major = MajorDao.getMajor(student.getMajorName());
			
			String sql="INSERT INTO studb VALUES("+student.getId()+",'"+student.getName()+"',"+gender+","+student.getAge()+","+class_id+","+major+");";
			System.err.println(sql);	
			statement.execute(sql);
			
		}catch(SQLException e) {
			e.printStackTrace();
		}
		
	}	
	
	//删除学生
	public static void del(String name) {
		Connection conn = ConnectionUtil.getConnection();
		
		String sql = "DELETE FROM studb WHERE name = '"+name+"'";
		
		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if(result >0 ) {
				System.out.println("删除成功");
			}else {
				System.out.println("删除失败");
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
	}

	//更新学生信息
	public static void update(String studname, int column, Object value) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = null;
		switch(column) {
		case 1:
			sql = "UPDATE studb SET id = '"+value+"' WHERE name = '"+studname+"'";
			break;
		case 2:
			sql = "UPDATE studb SET name = '"+value+"' WHERE name = '"+studname+"'";
			break;
		case 3:
			if(value.equals("男")) {
				value = 1;
			}else {
			    value = 0;
			}
			sql = "UPDATE studb SET  sex= '"+value+"' WHERE name = '"+studname+"'";
			break;
		case 4:
			sql = "UPDATE studb SET age = '"+value+"' WHERE name = '"+studname+"'";
			break;
		case 5:
			value = ClassDao.getClassId(value);
			sql = "UPDATE studb SET classid = '"+value+"' WHERE name = '"+studname+"'";
			break;
		case 6:
			value = MajorDao.getMajor(value);
			sql = "UPDATE studb SET major = '"+value+"' WHERE name = '"+studname+"'";
			break;
		default:
			System.out.println("输入有误！");
			break;
		}
		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if(result>0) {
				System.out.println("更新成功");
			}else {
				System.out.println("更新失败");
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	//通过名字获取id
	public static int getStuIdWithName(String stu_name) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "SELECT id from studb WHERE name='"+stu_name+"'";
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			if(rs.next()) {
				int id = rs.getInt(1);
				return id;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return 1100;
	}
}

